package bid.dao.impl;

import bid.dao.DepartDao;
import bid.entity.Department;
import bid.entity.Page;
import bid.entity.User;
import bid.util.DBUtil;
import bid.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 */
public class DepartDaoImpl implements DepartDao {
    @Override
    public int add(Department ur) {

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "insert into department(departmentName)values (?)";
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setString(1, ur.getDepartmentname());
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public int del(long id) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "delete FROM department where id = ?";

//        sql += " where id = ?";
        try {
            //sql参数
            st = con.prepareStatement(sql);
            //填充占位符

            st.setLong(1, id);
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public int update(Department ur) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "update department set " +
                "departmentName = ? where id = ?";

        try {
            //sql参数
            st = con.prepareStatement(sql);
            //填充占位符
            st.setString(1, ur.getDepartmentname());
            st.setLong(2, ur.getId());

            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public List<Department> findAll() {
        String sql = "select * from department  ";
        List<Department> users = new ArrayList<>();
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符

            rs = st.executeQuery();
            while (rs.next()) {
                Department user = new Department();
                user.setId(rs.getLong("id"));
                user.setDepartmentname(rs.getString("departmentName"));

                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return users;

    }

    @Override
    public List<Department> search(int pageNo, int pageSize) {
        String sql = "select * from department  limit ?,?";
        List<Department> users = new ArrayList<>();
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setLong(1, (pageNo - 1) * pageSize);
            st.setLong(2, pageSize);
            rs = st.executeQuery();
            while (rs.next()) {
                Department user = new Department();
                user.setId(rs.getLong("id"));
                user.setDepartmentname(rs.getString("departmentName"));

                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return users;
    }

    @Override
    public Page<Department> paging(int pageNo, int pageSize) {
        List<Department> list = this.search(pageNo, pageSize);
        long total = this.searchTotal();
        return new Page<Department>(pageNo,pageSize,total,list);
    }

    @Override
    public Department get(long id) {
        String sql = "select * from department where id=?";

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setLong(1, id);
            rs = st.executeQuery();
            if (rs.next()) {
                Department user = new Department();
                user.setId(rs.getLong("id"));
                user.setDepartmentname(rs.getString("departmentName"));

                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return null;
    }

    @Override
    public long searchTotal() {
        String sql = "select count(id) from department where 1=1";
        List<Object> params = new ArrayList<>();


        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            for (int i = 0; i < params.size(); i++) {
                st.setObject(i + 1, params.get(i));
            }
            rs = st.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return 0L;
    }
}
